#!/usr/bin/env node
/*
 * 通用历史数据导入脚本
 * 功能：
 *  - 从 Excel/CSV 读取（使用 xlsx）
 *  - 字段名映射（支持配置 + 内置同义词）
 *  - 值转换（日期/数字/缺省）
 *  - 输出 SQL 文件或直接写入 SQLite（UPSERT）
 * 用法（Windows CMD 示例）：
 *  node backend\scripts\import-historical.cjs --type estimates --file "D:\\data\\历史预估值.xlsx" --mode direct
 *  node backend\scripts\import-historical.cjs --type targets --file "D:\\data\\历史目标值.csv" --mode sql --output D:\\out.sql
 *  node backend\scripts\import-historical.cjs --type estimates --file .\\data\\hist.xlsx --config .\\scripts\\historical-import.config.json --mode direct
 */
const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
const sqlite3 = require('sqlite3').verbose();

// --- 简易参数解析 ---
const argv = process.argv.slice(2);
function getArg(name, def) {
  const i = argv.findIndex(a => a === `--${name}`);
  if (i >= 0 && argv[i+1]) return argv[i+1];
  const kv = argv.find(a => a.startsWith(`--${name}=`));
  if (kv) return kv.split('=')[1];
  return def;
}

const TYPE = getArg('type'); // 'targets' | 'estimates'
const FILE = getArg('file');
const MODE = getArg('mode', 'direct'); // 'direct' | 'sql'
const OUTPUT = getArg('output', path.resolve(process.cwd(), 'import.sql'));
const CONFIG = getArg('config');
const STRICT = getArg('strict', 'false') === 'true'; // 严格模式：缺失必填则跳过

if (!TYPE || !FILE) {
  console.error('用法: node backend/scripts/import-historical.cjs --type <targets|estimates> --file <excel_or_csv_path> [--mode direct|sql] [--output out.sql] [--config mapping.json] [--strict true|false]');
  process.exit(1);
}

// --- 加载配置 ---
let userConfig = null;
if (CONFIG && fs.existsSync(CONFIG)) {
  userConfig = JSON.parse(fs.readFileSync(CONFIG, 'utf8'));
}

// --- 内置同义词字典 ---
const synonyms = {
  material_code: ['物料编码','物料号','物料代码','物料','编码','料号','料码'],
  product_name: ['产品名称','品名','产品','名称','品名/规格'],
  product_series: ['产品系列','系列'],
  record_date: ['记录日期','日期','生效日期','有效开始','开始日期'],
  takt_time: ['节拍','标准节拍','节拍(s)','节拍秒'],
  whole_machine_target: ['整机目标值','整机PPH','目标PPH','目标值'],
  motor_target: ['整机电机目标值','电机目标值','电机PPH'],
  standard_staff: ['标准人数','人数','标准人力'],
  line_name: ['生产线','线别','线','产线','线体','线名称'],
  estimate_pph: ['预估PPH值','预估PPH','预估值','PPH预估值','估算PPH'],
  order_number: ['工单号','订单号','生产单号','工单'],
  legacy_actual_pph: ['旧实际PPH','旧PPH','历史PPH','PPH(旧)','PPH旧值'],
  legacy_actual_takt_time: ['旧实际节拍','历史节拍','节拍(旧)','节拍旧值','旧节拍'],
  calc_version: ['算法版本','计算版本','旧算法版本','来源版本'],
  use_legacy_override: ['使用旧值','覆盖为旧值','使用历史值','用旧算法']
};

// --- 字段映射加载：优先配置文件，其次内置同义词 ---
function buildMapper(type, headers, config) {
  const map = {};
  const lowerHeaders = headers.map(h => (h||'').toString().trim());

  function findHeaderFor(targetKey) {
    // 1) 配置文件优先
    const conf = config && config[type] && config[type].columns;
    if (conf) {
      const src = conf[targetKey];
      if (src) {
        const hit = lowerHeaders.find(h => h.toLowerCase() === src.toLowerCase());
        if (hit) return hit;
      }
    }
    // 2) 同义词匹配
    const syns = synonyms[targetKey] || [];
    for (const cand of syns) {
      const hit = lowerHeaders.find(h => h.replace(/[\s_-]/g,'').toLowerCase() === cand.replace(/[\s_-]/g,'').toLowerCase());
      if (hit) return hit;
    }
    // 3) 直接同名
    const exact = lowerHeaders.find(h => h === targetKey || h.toLowerCase() === targetKey.toLowerCase());
    if (exact) return exact;
    return null;
  }

  let targetKeys;
  if (type === 'targets') {
    targetKeys = ['material_code','product_name','product_series','record_date','takt_time','whole_machine_target','motor_target','standard_staff','line_name'];
  } else if (type === 'estimates') {
    targetKeys = ['material_code','product_name','line_name','estimate_pph'];
  } else if (type === 'records-legacy') {
    targetKeys = ['record_date','line_name','order_number','legacy_actual_pph','legacy_actual_takt_time','calc_version','use_legacy_override'];
  } else {
    throw new Error(`Unknown type: ${type}`);
  }

  for (const key of targetKeys) {
    map[key] = findHeaderFor(key);
  }

  const defaults = (config && config[type] && config[type].defaults) || {};
  return { map, defaults };
}

// --- 工具函数 ---
function escapeSQLValue(v) {
  if (v === null || v === undefined) return 'NULL';
  if (typeof v === 'number') return String(v);
  return `'${String(v).replace(/'/g, "''")}'`;
}

function toNumber(v) {
  if (v === null || v === undefined || v === '') return null;
  const n = Number(String(v).toString().replace(/[,\s]/g,'').trim());
  return isNaN(n) ? null : n;
}
function toInt(v) {
  const n = toNumber(v);
  return n == null ? null : Math.trunc(n);
}
function excelSerialToISO(n) {
  try {
    // Excel 序列到 JS Date（1900系统）
    const epoch = new Date(Date.UTC(1899, 11, 30));
    const ms = Math.round((n) * 24 * 60 * 60 * 1000);
    const d = new Date(epoch.getTime() + ms);
    const y = d.getUTCFullYear();
    const m = String(d.getUTCMonth()+1).padStart(2,'0');
    const dd = String(d.getUTCDate()).padStart(2,'0');
    return `${y}-${m}-${dd}`;
  } catch {
    return null;
  }
}
function toISODate(v) {
  if (v == null || v === '') return null;
  if (typeof v === 'number') {
    if (v >= 1 && v <= 80000) return excelSerialToISO(v);
  }
  const s = String(v).trim();
  if (/^(?:\d{4}[-/]\d{1,2}[-/]\d{1,2})$/.test(s)) {
    const [y, m, d] = s.replace(/\//g,'-').split('-');
    return `${y.padStart(4,'0')}-${m.padStart(2,'0')}-${d.padStart(2,'0')}`;
  }
  // 纯数字序列
  if (/^\d{5}$/.test(s) || /^\d{1,5}$/.test(s)) return excelSerialToISO(Number(s));
  return s; // 原样返回，交给DB验证
}

function pick(row, srcKey, def) {
  if (!srcKey) return def ?? null;
  const v = row[srcKey];
  return (v === undefined || v === null || v === '') ? (def ?? null) : v;
}

function normalizeRow(type, row, mapper) {
  const { map, defaults } = mapper;
  if (type === 'targets') {
    const obj = {
      material_code: String(pick(row, map.material_code, defaults.material_code || '')).trim(),
      product_name: String(pick(row, map.product_name, defaults.product_name || '未知产品')).trim(),
      product_series: pick(row, map.product_series, defaults.product_series || null),
      record_date: toISODate(pick(row, map.record_date, defaults.record_date || null)),
      takt_time: toNumber(pick(row, map.takt_time, defaults.takt_time || null)),
      whole_machine_target: toNumber(pick(row, map.whole_machine_target, defaults.whole_machine_target || null)),
      motor_target: toNumber(pick(row, map.motor_target, defaults.motor_target || null)),
      standard_staff: toInt(pick(row, map.standard_staff, defaults.standard_staff || null)),
      line_name: String(pick(row, map.line_name, defaults.line_name || '')).trim()
    };
    return obj;
  }
  if (type === 'estimates') {
    const obj = {
      material_code: String(pick(row, map.material_code, defaults.material_code || '')).trim(),
      product_name: String(pick(row, map.product_name, defaults.product_name || '未知产品')).trim(),
      line_name: String(pick(row, map.line_name, defaults.line_name || '')).trim(),
      estimate_pph: toNumber(pick(row, map.estimate_pph, defaults.estimate_pph || null))
    };
    return obj;
  }
  // records-legacy
  return {
    record_date: toISODate(pick(row, map.record_date, defaults.record_date || null)),
    line_name: String(pick(row, map.line_name, defaults.line_name || '')).trim(),
    order_number: String(pick(row, map.order_number, defaults.order_number || '')).trim(),
    legacy_actual_pph: toNumber(pick(row, map.legacy_actual_pph, defaults.legacy_actual_pph || null)),
    legacy_actual_takt_time: toNumber(pick(row, map.legacy_actual_takt_time, defaults.legacy_actual_takt_time || null)),
    calc_version: String(pick(row, map.calc_version, defaults.calc_version || 'legacy-import-v1')).trim(),
    use_legacy_override: (() => {
      const v = pick(row, map.use_legacy_override, defaults.use_legacy_override);
      if (v === undefined || v === null || v === '') return 1; // 默认启用覆盖
      const s = String(v).trim().toLowerCase();
      return (s === '1' || s === 'true' || s === '是' || s === 'y') ? 1 : 0;
    })()
  };
}

function validateTargetRow(obj) {
  const errors = [];
  if (!obj.material_code) errors.push('material_code 缺失');
  if (!obj.line_name) errors.push('line_name 缺失');
  if (!obj.record_date) errors.push('record_date 缺失');
  if (obj.takt_time == null) errors.push('takt_time 缺失');
  if (obj.standard_staff == null) errors.push('standard_staff 缺失');
  return errors;
}

function validateEstimateRow(obj) {
  const errors = [];
  if (!obj.material_code) errors.push('material_code 缺失');
  if (!obj.line_name) errors.push('line_name 缺失');
  if (obj.estimate_pph == null) errors.push('estimate_pph 缺失');
  if (!obj.product_name) obj.product_name = '未知产品';
  return errors;
}

function validateRow(type, obj) {
  if (type === 'targets') return validateTargetRow(obj);
  if (type === 'estimates') return validateEstimateRow(obj);
  // records-legacy
  const errors = [];
  if (!obj.record_date) errors.push('record_date 缺失');
  if (!obj.line_name) errors.push('line_name 缺失');
  if (!obj.order_number) errors.push('order_number 缺失');
  if (obj.legacy_actual_pph == null && obj.legacy_actual_takt_time == null) errors.push('至少需要 legacy_actual_pph 或 legacy_actual_takt_time');
  return errors;
}

function readRows(file) {
  const wb = XLSX.readFile(file, { cellDates: false });
  const sheet = wb.Sheets[wb.SheetNames[0]];
  const rows = XLSX.utils.sheet_to_json(sheet, { defval: '' });
  return rows;
}

function buildSQL(type, rows) {
  const stmts = [];
  stmts.push('BEGIN;');
  if (type === 'targets') {
    for (const r of rows) {
      const vals = [
        r.material_code, r.product_name, r.product_series, r.record_date,
        r.takt_time, r.whole_machine_target, r.motor_target, r.standard_staff, r.line_name
      ].map(v => (v === null || v === undefined ? null : v));
      const esc = vals.map(escapeSQLValue);
      stmts.push(`INSERT INTO pph_targets (material_code, product_name, product_series, record_date, takt_time, whole_machine_target, motor_target, standard_staff, line_name)
VALUES (${esc.join(', ')})
ON CONFLICT(material_code, line_name, record_date) DO UPDATE SET
  product_name=excluded.product_name,
  product_series=excluded.product_series,
  takt_time=excluded.takt_time,
  whole_machine_target=excluded.whole_machine_target,
  motor_target=excluded.motor_target,
  standard_staff=excluded.standard_staff,
  updated_at=CURRENT_TIMESTAMP;`);
    }
  } else if (type === 'estimates') {
    for (const r of rows) {
      const vals = [r.material_code, r.product_name, r.line_name, r.estimate_pph].map(v => (v == null ? null : v));
      const esc = vals.map(escapeSQLValue);
      stmts.push(`INSERT INTO pph_estimates (material_code, product_name, line_name, estimate_pph)
VALUES (${esc.join(', ')})
ON CONFLICT(material_code, line_name) DO UPDATE SET
  product_name=excluded.product_name,
  estimate_pph=excluded.estimate_pph,
  updated_at=CURRENT_TIMESTAMP;`);
    }
  } else if (type === 'records-legacy') {
    for (const r of rows) {
      const sets = [];
      if (r.legacy_actual_pph != null) sets.push(`legacy_actual_pph = ${escapeSQLValue(r.legacy_actual_pph)}`);
      if (r.legacy_actual_takt_time != null) sets.push(`legacy_actual_takt_time = ${escapeSQLValue(r.legacy_actual_takt_time)}`);
      if (r.calc_version) sets.push(`calc_version = ${escapeSQLValue(r.calc_version)}`);
      sets.push(`use_legacy_override = ${escapeSQLValue(r.use_legacy_override)}`);
      const where = [
        `record_date = ${escapeSQLValue(r.record_date)}`,
        `line_name = ${escapeSQLValue(r.line_name)}`,
        `order_number = ${escapeSQLValue(r.order_number)}`
      ].join(' AND ');
      stmts.push(`UPDATE pph_records SET ${sets.join(', ')} WHERE ${where};`);
    }
  }
  stmts.push('COMMIT;');
  return stmts.join('\n');
}

async function writeDirect(type, rows) {
  const dbPath = path.join(__dirname, '..', 'data', 'pph.sqlite');
  const db = new sqlite3.Database(dbPath);
  function run(sql, params=[]) {return new Promise((resolve,reject)=>db.run(sql, params, function(e){e?reject(e):resolve(this)}));}
  try {
    await run('BEGIN');
  if (type === 'targets') {
      const sql = `INSERT INTO pph_targets (material_code, product_name, product_series, record_date, takt_time, whole_machine_target, motor_target, standard_staff, line_name)
        VALUES (?,?,?,?,?,?,?,?,?)
        ON CONFLICT(material_code, line_name, record_date) DO UPDATE SET
          product_name=excluded.product_name,
          product_series=excluded.product_series,
          takt_time=excluded.takt_time,
          whole_machine_target=excluded.whole_machine_target,
          motor_target=excluded.motor_target,
          standard_staff=excluded.standard_staff,
          updated_at=CURRENT_TIMESTAMP`;
      for (const r of rows) {
        await run(sql, [r.material_code, r.product_name, r.product_series, r.record_date, r.takt_time, r.whole_machine_target, r.motor_target, r.standard_staff, r.line_name]);
      }
  } else if (type === 'estimates') {
      const sql = `INSERT INTO pph_estimates (material_code, product_name, line_name, estimate_pph)
        VALUES (?,?,?,?)
        ON CONFLICT(material_code, line_name) DO UPDATE SET
          product_name=excluded.product_name,
          estimate_pph=excluded.estimate_pph,
          updated_at=CURRENT_TIMESTAMP`;
      for (const r of rows) {
        await run(sql, [r.material_code, r.product_name, r.line_name, r.estimate_pph]);
      }
    } else if (type === 'records-legacy') {
      for (const r of rows) {
        const sets = [];
        const params = [];
        if (r.legacy_actual_pph != null) { sets.push('legacy_actual_pph = ?'); params.push(r.legacy_actual_pph); }
        if (r.legacy_actual_takt_time != null) { sets.push('legacy_actual_takt_time = ?'); params.push(r.legacy_actual_takt_time); }
        if (r.calc_version) { sets.push('calc_version = ?'); params.push(r.calc_version); }
        sets.push('use_legacy_override = ?'); params.push(r.use_legacy_override);
        const sql = `UPDATE pph_records SET ${sets.join(', ')} WHERE record_date = ? AND line_name = ? AND order_number = ?`;
        await run(sql, [...params, r.record_date, r.line_name, r.order_number]);
      }
    }
    await run('COMMIT');
  } catch (e) {
    try { await run('ROLLBACK'); } catch {}
    throw e;
  } finally {
    db.close();
  }
}

function hasCriticalMissing(type, obj) {
  return type === 'targets'
    ? (!obj.material_code || !obj.line_name || !obj.record_date || obj.takt_time == null || obj.standard_staff == null)
    : (!obj.material_code || !obj.line_name || obj.estimate_pph == null);
}

function normalizeAndValidate(rows, type, mapper, strictMode) {
  const normalized = [];
  const skipped = [];
  for (let i = 0; i < rows.length; i++) {
    const raw = rows[i];
    const obj = normalizeRow(type, raw, mapper);
    const errs = validateRow(type, obj);
    if (errs.length > 0) {
      if (strictMode) {
        skipped.push({ index: i + 2, errors: errs });
        continue;
      }
      if (hasCriticalMissing(type, obj)) {
        skipped.push({ index: i + 2, errors: errs });
        continue;
      }
    }
    normalized.push(obj);
  }
  return { normalized, skipped };
}

function outputSQL(normalized, skipped, outputPath, type) {
  const sql = buildSQL(type, normalized);
  fs.writeFileSync(outputPath, sql, 'utf8');
  console.log(`✅ 已生成 SQL: ${outputPath}`);
  console.log(`可使用 SQLite 工具执行，或在脚本中执行。有效行: ${normalized.length}，跳过: ${skipped.length}`);
}

async function writeDB(normalized, skipped, type) {
  await writeDirect(type, normalized);
  console.log(`✅ 已写入 SQLite。有效行: ${normalized.length}，跳过: ${skipped.length}`);
}

function logSkipped(skipped) {
  if (skipped.length) {
    console.warn('⚠️ 有跳过的行（仅展示前10条）：');
    console.warn(JSON.stringify(skipped.slice(0, 10), null, 2) + (skipped.length > 10 ? '\n...' : ''));
  }
}

async function main() {
  const rows = readRows(FILE);
  if (!rows || rows.length === 0) {
    console.error('输入文件为空');
    process.exit(1);
  }
  const headers = Object.keys(rows[0] || {});
  const mapper = buildMapper(TYPE, headers, userConfig);
  const { normalized, skipped } = normalizeAndValidate(rows, TYPE, mapper, STRICT);

  if (normalized.length === 0) {
    console.error('没有可导入的有效行。跳过行:', skipped.length);
    if (skipped.length) console.error(JSON.stringify(skipped.slice(0, 10), null, 2) + (skipped.length > 10 ? '\n...' : ''));
    process.exit(1);
  }

  if (MODE === 'sql') {
    outputSQL(normalized, skipped, OUTPUT, TYPE);
  } else {
    await writeDB(normalized, skipped, TYPE);
  }

  logSkipped(skipped);
}

(async () => {
  try {
    await main();
  } catch (e) {
    console.error('❌ 导入失败:', e && (e.message || e));
    process.exit(1);
  }
})();
